﻿# -------------------------------- #
# SQL Server AwaysOn Configuration #
# -------------------------------- #
param([string]$virtualMachineService, [string]$virtualMachineName)

# Return availability group replica version
function Get-ReplicaVersion($version)
{
    switch ($version)
    {
        "2012"
        {
		    return "11"
        }
        "2014"
        {
		    return "12"
        }
        default
        {
            throw "SQL Server version '$version' is not supported."
        }
    }
}

Write-Information "Virtual machine '$virtualMachineName' executing SQL Server AlwaysOn '$($script.type)' configuration ... " -NoNewLine $true
$userName = $subscription.virtualMachines.adminUserName
$password = $subscription.virtualMachines.password
$netBiosDomain = $subscription.virtualMachines.netBiosDomain
$uri = Get-AzureWinRMUri -ServiceName $virtualMachineService -Name $virtualMachineName
$credential = New-Object System.Management.Automation.PSCredential(($netBiosDomain + "\" + $userName), (ConvertTo-SecureString $password -AsPlainText -Force))
$session = New-PSSession -ComputerName $uri[0].DnsSafeHost -Credential $credential -Port (Get-VirtualMachineInstanceEndpointPort $subscription $virtualMachineName) -UseSSL -Authentication Credssp
switch ($script.type)
{
	"Create"
	{
		Invoke-Command -Session $session -ScriptBlock {
			param([string]$availabilityGroup, [string]$endpointName, [string]$version, [string]$availabilityMode, [string]$failoverMode)
			Import-Module "sqlps" -DisableNameChecking
			# Enable AlwaysOn
			Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\$Env:COMPUTERNAME\Default -Force
			# Create HADR Endpoint
			$endpoint = (dir "SQLSERVER:\SQL\$Env:COMPUTERNAME\Default\endpoints") | ? {$_.Name -eq $endpointName}
			if ($endpoint -eq $null)
			{
				$endpoint = New-SqlHadrEndpoint $endpointName -Port 5022 -Path "SQLSERVER:\SQL\$Env:COMPUTERNAME\Default"
			}
			Set-SqlHadrEndpoint -InputObject $endpoint -State "Started" | Out-Null
			$sqlServiceAccount = (Get-WmiObject Win32_Service -Filter "Name='MSSQLSERVER'").StartName
			Invoke-SqlCmd -Query "IF NOT EXISTS(SELECT * FROM master.sys.syslogins WHERE name = '$sqlServiceAccount') CREATE LOGIN [$sqlServiceAccount] FROM WINDOWS;" -ServerInstance $Env:COMPUTERNAME
			Invoke-SqlCmd -Query "GRANT CONNECT ON ENDPOINT::[$endpointName] TO [$sqlServiceAccount]" -ServerInstance $Env:COMPUTERNAME
			# Create AlwaysOn Availability Group
			$replica = New-SqlAvailabilityReplica -Name $Env:COMPUTERNAME -EndpointURL "TCP://$Env:COMPUTERNAME.$($Env:USERDNSDOMAIN):5022" -AvailabilityMode $availabilityMode -FailoverMode $failoverMode -Version $version -AsTemplate -ConnectionModeInSecondaryRole AllowAllConnections
            if ((dir "SQLSERVER:\SQL\$Env:COMPUTERNAME\Default\AvailabilityGroups" | ? {$_.Name -eq $availabilityGroup}) -eq $null)
            {
			    New-SqlAvailabilityGroup -Name $availabilityGroup -Path "SQLSERVER:\SQL\$Env:COMPUTERNAME\Default" -AvailabilityReplica $replica | Out-Null
            }
		} -ArgumentList $script.availabilityGroup, $script.endPoint, (Get-ReplicaVersion $script.version), $script.availabilityMode, $script.failoverMode
		Remove-PSSession $session
	}
	"Add Replica"
	{
		Invoke-Command -Session $session -ScriptBlock {
			param([string]$server, [string]$availabilityGroup, [string]$endpointName, [string]$availabilityMode, [string]$failoverMode)
			Import-Module "sqlps" -DisableNameChecking
            if ((dir "SQLSERVER:\SQL\$Env:COMPUTERNAME\Default\AvailabilityGroups\$availabilityGroup\AvailabilityReplicas" | ? {$_.Name -eq $server}) -eq $null)
            {
                $replica = New-SqlAvailabilityReplica -Name $server.ToUpper() `
                    -Path "SQLSERVER:\SQL\$Env:COMPUTERNAME\Default\AvailabilityGroups\$availabilityGroup" `
                    -EndpointUrl "TCP://$server.$($Env:USERDNSDOMAIN):5022" `
                    -AvailabilityMode $availabilityMode -FailoverMode $failoverMode -ConnectionModeInSecondaryRole AllowAllConnections
            }
		} -ArgumentList $script.name, $script.availabilityGroup, $script.endPoint, $script.availabilityMode, $script.failoverMode
		Remove-PSSession $session
	}
	"Join Replica"
	{
		Invoke-Command -Session $session -ScriptBlock {
			param([string]$availabilityGroup, [string]$endpointName)
			Import-Module "sqlps" -DisableNameChecking
			# Enable AlwaysOn
			Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\$Env:COMPUTERNAME\Default -Force
			# Create HADR Endpoint
			$endpoint = (dir "SQLSERVER:\SQL\$Env:COMPUTERNAME\Default\endpoints") | ? {$_.Name -eq $endpointName}
			if ($endpoint -eq $null)
			{
				$endpoint = New-SqlHadrEndpoint $endpointName -Port 5022 -Path "SQLSERVER:\SQL\$Env:COMPUTERNAME\Default"
			}
			Set-SqlHadrEndpoint -InputObject $endpoint -State "Started" | Out-Null
			$sqlServiceAccount = (Get-WmiObject Win32_Service -Filter "Name='MSSQLSERVER'").StartName
			Invoke-SqlCmd -Query "IF NOT EXISTS(SELECT * FROM master.sys.syslogins WHERE name = '$sqlServiceAccount') CREATE LOGIN [$sqlServiceAccount] FROM WINDOWS;" -ServerInstance $Env:COMPUTERNAME
			Invoke-SqlCmd -Query "GRANT CONNECT ON ENDPOINT::[$endpointName] TO [$sqlServiceAccount]" -ServerInstance $Env:COMPUTERNAME
			# Join AlwaysOn Availability Group
            if ((dir "SQLSERVER:\SQL\$Env:COMPUTERNAME\Default\AvailabilityGroups" | ? {$_.Name -eq $availabilityGroup}) -eq $null)
            {
			    Join-SqlAvailabilityGroup -Name $availabilityGroup -Path "SQLSERVER:\SQL\$Env:COMPUTERNAME\Default"
            }
		} -ArgumentList $script.availabilityGroup, $script.endPoint
		Remove-PSSession $session
	}
	"Listener"
	{
        if ($script.listenerName.Length -gt 15) { throw "Listener name '$($script.listenerName)' length cannot exceed 15 characters." }
		$ipAddress = [System.Net.Dns]::GetHostAddresses($uri[0].DnsSafeHost).IPAddressToString
        if ($script.listenerIPAddress -ne $null)
        {
            $ipAddress = $script.listenerIPAddress
        }
		Invoke-Command -Session $session -ScriptBlock {
			param([string]$availabilityGroup, [string]$listenerName, [string]$ipAddress, [string]$listenerPort, [string]$probePort)
			Import-Module FailoverClusters
			Import-Module "sqlps" -DisableNameChecking
            if ((Get-ClusterResource | ? {$_.Name -eq $listenerName}) -eq $null)
            {
			    # Create IP address
			    Add-ClusterResource "IP Address $ipAddress" -ResourceType "IP Address" -Group $availabilityGroup | Set-ClusterParameter -Multiple @{"Address"="$ipAddress"; "ProbePort"="$probePort"; "SubnetMask"="255.255.255.255"; "Network"="$((Get-ClusterNetwork)[0].Name)"; "OverrideAddressMatch"=1; "EnableDhcp"="0"} | Out-Null
			    # Create listener network name
			    Add-ClusterResource -Name $listenerName -ResourceType "Network Name" -Group $availabilityGroup | Set-ClusterParameter -Multiple @{"Name"="$listenerName"; "DnsName"="$listenerName"} | Out-Null
			    # Set network name dependency on IP address
			    Get-ClusterGroup $availabilityGroup | Get-ClusterResource | ? {$_.Name -eq $listenerName} | Set-ClusterResourceDependency "[IP Address $ipAddress]" | Out-Null
			    # Start network name
			    Start-ClusterResource -Name $listenerName | Out-Null
			    # Set availability group dependency on network name
                Stop-ClusterResource -Name $availabilityGroup | Out-Null
			    Get-ClusterResource -Name $availabilityGroup | Set-ClusterResourceDependency "[$listenerName]" | Out-Null
                Start-ClusterResource -Name $availabilityGroup | Out-Null
            }
			# Set availability group listener port
            Set-SqlAvailabilityGroupListener -Path SQLSERVER:\SQL\$Env:COMPUTERNAME\Default\AvailabilityGroups\$availabilityGroup\AvailabilityGroupListeners\$listenerName -Port $listenerPort | Out-Null
		} -ArgumentList $script.availabilityGroup, $script.listenerName, $ipAddress, $script.listenerPort, $script.probePort
		Remove-PSSession $session
		# Test ILB listener: sqlcmd -S "<ListenerName>,<EndpointPort>" -d "<DatabaseName>" -Q "select @@servername, db_name()" -l 15
		# Test LB listener: sqlcmd -S "mycloudservice.cloudapp.net,<EndpointPort>" -d "<DatabaseName>" -U "<LoginId>" -P "<Password>"  -Q "select @@servername, db_name()" -l 15
	}
	"Database"
	{
		$uri = Get-AzureWinRMUri -ServiceName $virtualMachineService -Name $virtualMachineName
		$session = New-PSSession -ComputerName $uri[0].DnsSafeHost -Credential $credential -Port (Get-VirtualMachineInstanceEndpointPort $subscription $virtualMachineName) -UseSSL -Authentication Credssp
		foreach ($database in $script.databases.database)
		{
			Invoke-Command -Session $session -ScriptBlock {
				param([string]$server, [string]$availabilityGroup, [string]$database, [string]$networkShare)
				Import-Module "sqlps" -DisableNameChecking
				# Set database recovery model
				Invoke-Sqlcmd -Query "ALTER DATABASE [$database] SET RECOVERY FULL"
				# Perform full database backup
				Backup-SqlDatabase -Database $database -BackupFile "$($networkShare)\$($database).bak" -ServerInstance $server -Initialize
				Backup-SqlDatabase -Database $database -BackupFile "$($networkShare)\$($database).log" -ServerInstance $server -BackupAction Log -Initialize
				# Add database to availability group
                if ((dir "SQLSERVER:\SQL\$server\Default\AvailabilityGroups\$availabilityGroup\AvailabilityDatabases" | ? {$_.Name -eq $database}) -eq $null)
                {
				    Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$server\Default\AvailabilityGroups\$availabilityGroup" -Database $database | Out-Null
                }
			} -ArgumentList $virtualMachineName, $script.availabilityGroup, $database.name, $script.networkShare
		}
		Remove-PSSession $session
		foreach ($replica in $script.replicas.replica)
		{
			$virtualMachineName = $replica.Name
			$virtualMachineService = ($subscription.virtualMachines.virtualMachine | ? {$_.name -eq $replica.name}).service
			$uri = Get-AzureWinRMUri -ServiceName $virtualMachineService -Name $virtualMachineName
			$session = New-PSSession -ComputerName $uri[0].DnsSafeHost -Credential $credential -Port (Get-VirtualMachineInstanceEndpointPort $subscription $virtualMachineName) -UseSSL -Authentication Credssp
			foreach ($database in $script.databases.database)
			{
				Invoke-Command -Session $session -ScriptBlock {
					param([string]$server, [string]$availabilityGroup, [string]$database, [string]$networkShare)
					Import-Module "sqlps" -DisableNameChecking
                    if ((dir "SQLSERVER:\SQL\$server\Default\AvailabilityGroups\$availabilityGroup\AvailabilityDatabases" | ? {$_.Name -eq $database -and $_.IsJoined -eq $true}) -eq $null)
                    {
					    # Restore database
					    Restore-SqlDatabase -Database $database -BackupFile "$($networkShare)\$($database).bak" -ServerInstance $server -NoRecovery -ReplaceDatabase
					    Restore-SqlDatabase -Database $database -BackupFile "$($networkShare)\$($database).log" -ServerInstance $server -RestoreAction Log -NoRecovery
					    # Add database to availability group
					    Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$server\Default\AvailabilityGroups\$availabilityGroup" -Database $database | Out-Null
                    }
				} -ArgumentList $virtualMachineName, $script.availabilityGroup, $database.name, $script.networkShare
			}
			Remove-PSSession $session
		}
	}
	default
	{
		throw "SQL Server AlwaysOn type '$($script.type)' is not supported."
	}
}
Write-Text "done."